import datetime
import json

import cx_Oracle
import pandas as pd

"""递归查询oracle存储关联的存储和表"""

user = "pigpos"
passwd = "P1g#2023pos"
listener = 'CPGCNXL.cpchina.cn/CPGCNXL'
# listener = '10.240.24.135/NMDCFARM'
# listener = 'CTCNZQF.cpchina.cn/CTCNZQF'
conn = cx_Oracle.connect(user, passwd, listener)
cursor = conn.cursor()
table_set = {'1'}
path = "C:/Users/yangjianzhang/Desktop/"


def export_excel():
    export = []
    tabel_list = list(table_set)
    list.sort(tabel_list)
    for table_name in tabel_list:
        t = {'table_name': table_name}
        export.append(t)
        # print(table_name)
    print(export)
    pf = pd.DataFrame(list(export))
    order = ['table_name']
    pf = pf[order]
    file_path = pd.ExcelWriter(path + 'table_name.xlsx')
    pf.to_excel(file_path, encoding='utf-8', index=False)
    file_path.save()


def select(sql) -> json:
    rows = []
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = value
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    return js


# 关闭连接，释放资源
def disconnect():
    cursor.close()
    conn.close()
    print("关闭数据库连接")


"""查询用户是否分配配权限"""
if __name__ == '__main__':
    file_path = 'C:/Users/yangjianzhang/Desktop/饲喂历史-24-07-2024_15_39.xlsx'
    # 使用read_excel函数读取Excel文件并将其存储为DataFrame对象
    df = pd.read_excel(file_path)

    # 打印读取到的数据
    # print(df)
    print(type(df))
    swine_id_list = []
    swine_track_list = []
    for index, row in df.iterrows():
        swine_id = row['编号']
        swine_id_list.append(swine_id)
        swine_track = '无'
        # print(type(mobile))
        # print(swine_id)
        # 查询用户是否绑定了权限
        sql = "select swine_track from fr_mas_swine_information t where t.org_code = '083522111' and " \
              "t.farm_org = '411015-0-0' and t.swine_id = '{}'".format(swine_id)
        print(sql)
        rs = select(sql)
        rs = json.loads(rs)
        # print(rs)
        if len(rs) > 0:
            swine_track = rs[0]['SWINE_TRACK']
        swine_track_list.append(swine_track)

    excel_data = {}
    excel_data['耳号'] = swine_id_list
    excel_data['swineTrack'] = swine_track_list
    df = pd.DataFrame(excel_data)
    path = "C:/Users/yangjianzhang/Desktop/"
    df.to_excel(path + 'test.xlsx', index=False)
